﻿using CallCenter.Data.Base;
using CallCenter.Data.Models;
using CallCenter.Data.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.Hosting;

namespace CallCenter.Data.BizUtilities
{
    public class CompanyUtility : BaseUtility
    {

        public CompanyUtility(DataManager manager)
            : base(manager)
        { }


        /// <summary>
        /// Insert into ddlCompany table
        /// </summary>
        /// <param name="name"></param>
        /// <param name="legalName"></param>
        /// <param name="CreatedUser"></param>
        /// <param name="description"></param>
        /// <param name="phone"></param>
        /// <param name="fax"></param>
        /// <param name="streetOne"></param>
        /// <param name="streetTwo"></param>
        /// <param name="city"></param>
        /// <param name="stateId"></param>
        /// <param name="zip"></param>
        /// <param name="country"></param>
        /// <param name="website"></param>
        /// <param name="relationStart"></param>
        /// <param name="relationEnd"></param>
        /// <param name="companyTypeId"></param>
        /// <param name="clientTypeId"></param>
        /// <param name="timeZone"></param>
        /// <param name="notes"></param>
        /// <returns></returns>
        public int Insert( //define placeholders for the values in the table to insert
          string name,
          string legalName,
          string CreatedUser,
          string description,
          string phone,
          string fax,
          string streetOne,
          string streetTwo,
          string city,
          int stateId,
          string zip,
          string country,
          string website,
          Nullable<DateTime> relationStart,
           Nullable<DateTime> relationEnd,
          int companyTypeId,
          int clientTypeId,
          string timeZone,
          string notes
       )
        {

            int nRtn = -1;
        //    bool includeEndDate = relationEnd.CompareTo(DateTime.MinValue) != 0;
            StringBuilder sb = new StringBuilder();
            //string commandString = "";
            //SqlCommand cmdInsert = m_Connection.CreateCommand();
            //cmdInsert.CommandType = CommandType.Text;
            //sql command to insert the data in the table ccContacts            

            string commandString = "Insert INTO ddlCompany " +
                                "(name,legalName, CreatedUser, description, phone, fax, streetOne, streetTwo, " +
                                " city, stateId, country, website, relationStart, companyTypeId, clientTypeId, zip,";

           
                commandString += " relationEnd,";

            commandString += " notes, timeZone ) " +
                               " VALUES(@name, @legalName, @CreatedUser, @description, @phone, @fax, @streetOne, @streetTwo, " +
                               " @city, @stateId, @country, @website, @relationStart,@companyTypeId,@clientTypeId, @zip, ";

       
                commandString += " @relationEnd,";

            commandString += " @notes, @timeZone) " +
                               " SELECT @pResult =@@IDENTITY";


            using (var cmd = this.Factory.GetCommand(commandString))
            {
                this.Factory.AddParameter(cmd, "@name", SqlDbType.NVarChar, 100, name);
                this.Factory.AddParameter(cmd, "@legalName", SqlDbType.NVarChar, 100, legalName);
                this.Factory.AddParameter(cmd, "@CreatedUser", SqlDbType.NVarChar, 100, CreatedUser);
                this.Factory.AddParameter(cmd, "@description", SqlDbType.NVarChar, 1000, description);
                this.Factory.AddParameter(cmd, "@phone", SqlDbType.NVarChar, 20, phone);
                this.Factory.AddParameter(cmd, "@fax", SqlDbType.NVarChar, 100, fax);
                this.Factory.AddParameter(cmd, "@streetOne", SqlDbType.NVarChar, 50, streetOne);
                this.Factory.AddParameter(cmd, "@streetTwo", SqlDbType.NVarChar, 50, streetTwo);
                this.Factory.AddParameter(cmd, "@city", SqlDbType.NVarChar, 50, city);
                this.Factory.AddParameter(cmd, "@stateId", SqlDbType.Int, 10, stateId);
                this.Factory.AddParameter(cmd, "@zip", SqlDbType.NVarChar, 50, zip);
                this.Factory.AddParameter(cmd, "@country", SqlDbType.NVarChar, 50, country);
                this.Factory.AddParameter(cmd, "@website", SqlDbType.NVarChar, 100, website);
                //this.Factory.AddParameter(cmd, "@relationStart", SqlDbType.SmallDateTime, 10, relationStart);
                this.Factory.AddParameter(cmd, "@companyTypeId", SqlDbType.Int, 10, companyTypeId);
                this.Factory.AddParameter(cmd, "@clientTypeId", SqlDbType.Int, 10, clientTypeId);
                this.Factory.AddParameter(cmd, "@timeZone", SqlDbType.NVarChar, 10, timeZone);

                             
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);


                if ((relationStart == null) || (relationStart.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@relationStart", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@relationStart", SqlDbType.DateTime, 10, relationStart);

                if ((relationEnd == null) || (relationEnd.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@relationEnd", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@relationEnd", SqlDbType.DateTime, 10, relationEnd);



              
                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);


                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn; //retruning the data stored in the table

        }


        /// <summary>
        /// Insert into ddlCompany table
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ReturnMessage<CompanyGridViewModel> InsertToCompany(CompanyGridViewModel model)
        {
            var returnMessage = new ReturnMessage<CompanyGridViewModel>();


            if (String.IsNullOrEmpty(model.website))
                model.website = "";

            if (String.IsNullOrEmpty(model.name))
                model.name = "";

            if (String.IsNullOrEmpty(model.description))
                model.description = "";
            if (String.IsNullOrEmpty(model.phone))
                model.phone = "";

            if (String.IsNullOrEmpty(model.country))
                model.country = "";
            if (String.IsNullOrEmpty(model.zip))
                model.zip = "";
            if (String.IsNullOrEmpty(model.phone))
                model.phone = "";
            if (String.IsNullOrEmpty(model.city))
                model.city = "";
            if (String.IsNullOrEmpty(model.streetTwo))
                model.streetTwo = "";
            if (String.IsNullOrEmpty(model.address))
                model.address = "";
            if (String.IsNullOrEmpty(model.streetOne))
                model.streetOne = "";
            if (String.IsNullOrEmpty(model.fax))
                model.fax = "";
            if (String.IsNullOrEmpty(model.legalName))
                model.legalName = "";
            if (String.IsNullOrEmpty(model.timeZone))
                model.timeZone = "";
            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";

            string website = "None";
            if (model.website != "None")
                website = model.website;
            else
                website = "None";
            
            var id = this.Insert(model.name, model.legalName,model.CreatedUser, model.description, model.phone, model.fax, model.streetOne, model.streetTwo, model.city, model.stateId, model.zip, model.country, website, model.relationStart, model.relationEnd, model.companyTypeId, model.clientTypeId, model.timeZone, model.notes);

            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Message = "Recordhas been  inserted to database";
                returnMessage.Status = true;


            }
            return returnMessage;

        }


        /// <summary>
        /// update to ddlCompany table
        /// 
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ReturnMessage<CompanyGridViewModel> UpdateToCompany(CompanyGridViewModel model)
        {
            var returnMessage = new ReturnMessage<CompanyGridViewModel>();


            if (String.IsNullOrEmpty(model.website))
                model.website = "";

            if (String.IsNullOrEmpty(model.name))
                model.name = "";

            if (String.IsNullOrEmpty(model.description))
                model.description = "";
            if (String.IsNullOrEmpty(model.phone))
                model.phone = "";

            if (String.IsNullOrEmpty(model.country))
                model.country = "";
            if (String.IsNullOrEmpty(model.zip))
                model.zip = "";
            if (String.IsNullOrEmpty(model.phone))
                model.phone = "";
            if (String.IsNullOrEmpty(model.city))
                model.city = "";
            if (String.IsNullOrEmpty(model.streetTwo))
                model.streetTwo = "";
            if (String.IsNullOrEmpty(model.streetOne))
                model.streetOne = "";
            if (String.IsNullOrEmpty(model.fax))
                model.fax = "";
            if (String.IsNullOrEmpty(model.legalName))
                model.legalName = "";
            if (String.IsNullOrEmpty(model.timeZone))
                model.timeZone = "";
            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";

            string website = "None";
            if (model.website != "None")
                website = model.website;
            else
                website = "None";

          


            var id = this.Update(model.id, model.name, model.legalName, model.CreatedUser, model.description, model.phone, model.fax, model.streetOne, model.streetTwo, model.city, model.stateId, model.zip, model.country, website, model.relationStart, model.relationEnd, model.companyTypeId, model.clientTypeId, model.timeZone, model.notes);


            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Status = true;
                returnMessage.Message = "Recordhas been  inserted to database";
                this.Bridge.CompanyHistoryUtility.Insert(model.id, model.name, model.phone, model.fax, model.relationStart, model.relationEnd, model.companyTypeId, model.clientTypeId, DateTime.Now);
            }

            return returnMessage;
        }


        /// <summary>
        /// Delete from ddlCompany table
        /// </summary>
        /// <param name="nContactID"></param>
        public void Delete(int nContactID) //delete the row of data using the id passed to the function
        {
            string sql = "DELETE FROM ddlCompany " +
                                      "WHERE ID = @ID " +
                                      "DELETE FROM ddlCompanyContacts " +
                                      "WHERE ID = @ID ";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, nContactID);
                this.Factory.ExecuteNonQuery(cmd);
            }

        }

    
       /// <summary>
       /// Get Company Id by passing company name
       /// </summary>
       /// <param name="name"></param>
       /// <returns></returns>
               
        public int GetCompanyIdByName(string name)
        {
            int result = 0;
            string sql = string.Format("select id from ddlCompany where name = '{0}'", name);
            try
            {
                result = Convert.ToInt32(this.Factory.ExecuteScalar(sql));
            }
            catch { }
            return result;

        }
     
      
        /// <summary>
        /// Get Company name
        /// </summary>
        /// <returns></returns>
        public List<CompanyViewModel> GetJustName()
        {
            string sql = "SELECT id, name as cpName from ddlCompany where name like 'None'  " +
                  " UNION ALL select id, name as cpName FROM ddlCompany where name <> 'None' order by cpName ";
            return this.Factory.ExecuteObjects<CompanyViewModel>(sql);

        }


        /// <summary>
        /// Get Company name , photo, phone for look up
        /// </summary>
        /// <returns></returns>
        public List<CompanyViewModel> GetLookUpCompany()
        {
            string sql = "SELECT id, name, phone, website from ddlCompany where name like 'None'" +
                " UNION ALL select id, name, phone, website FROM ddlCompany where name <> 'None' ";
            return this.Factory.ExecuteObjects<CompanyViewModel>(sql);
        }


        /// <summary>
        /// Get All states from dblStates table
        /// </summary>
        /// <returns></returns>
        public List<StateViewModel> GetStates()
        {
            string sql = "SELECT s.id , s.state as stateName  FROM ddlStates s WHERE state like '--' UNION ALL SELECT s.id, s.state as stateName FROM ddlStates s WHERE state <> '--' Order By state";

            return this.Factory.ExecuteObjects<StateViewModel>(sql);

        }

        /// <summary>
        /// Update ddlCompany table
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="legalName"></param>
        /// <param name="CreatedUser"></param>
        /// <param name="description"></param>
        /// <param name="phone"></param>
        /// <param name="fax"></param>
        /// <param name="streetOne"></param>
        /// <param name="streetTwo"></param>
        /// <param name="city"></param>
        /// <param name="stateId"></param>
        /// <param name="zip"></param>
        /// <param name="country"></param>
        /// <param name="website"></param>
        /// <param name="relationStart"></param>
        /// <param name="relationEnd"></param>
        /// <param name="companyTypeId"></param>
        /// <param name="clientTypeId"></param>
        /// <param name="timeZone"></param>
        /// <param name="notes"></param>
        /// <returns></returns>
        public int Update(int id, string name, string legalName, string CreatedUser, string description,
          string phone, string fax, string streetOne, string streetTwo, string city, int stateId, string zip, string country, string website, Nullable<DateTime> relationStart, Nullable<DateTime> relationEnd, int companyTypeId, int clientTypeId, string timeZone, string notes)
        {

            int nRtn = -1;



            string sql = "UPDATE ddlCompany " +
                  "SET" +
                  " name=@name, legalName =@legalName,  CreatedUser = @CreatedUser, description=@description, phone=@phone, fax=@fax, streetOne=@streetOne, streetTwo=@streetTwo, " +
                  " city=@city, stateId=@stateId, country=@country, website=@website, " +
                  " relationStart=@relationStart, relationEnd=@relationEnd, " +
                  " notes=@notes, companyTypeId=@companyTypeId,clientTypeId=@clientTypeId, zip=@zip, timeZone=@timeZone WHERE id=@id";



            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@name", SqlDbType.NVarChar, 50, name);

                this.Factory.AddParameter(cmd, "@legalName", SqlDbType.NVarChar, 50, name);
                this.Factory.AddParameter(cmd, "@CreatedUser", SqlDbType.NVarChar, 50, CreatedUser);
                this.Factory.AddParameter(cmd, "@description", SqlDbType.NVarChar, 50, description);
                this.Factory.AddParameter(cmd, "@phone", SqlDbType.NVarChar, 50, phone);
                this.Factory.AddParameter(cmd, "@fax", SqlDbType.NVarChar, 50, phone);
                this.Factory.AddParameter(cmd, "@streetOne", SqlDbType.NVarChar, 10, streetOne);
                this.Factory.AddParameter(cmd, "@streetTwo", SqlDbType.NVarChar, 10, streetTwo);
                this.Factory.AddParameter(cmd, "@city", SqlDbType.NVarChar, 1000, city);
                this.Factory.AddParameter(cmd, "@zip", SqlDbType.NVarChar, 100, zip);
                this.Factory.AddParameter(cmd, "@stateId", SqlDbType.Int, 10, stateId);
                this.Factory.AddParameter(cmd, "@country", SqlDbType.NVarChar, 50, country);
                this.Factory.AddParameter(cmd, "@website", SqlDbType.NVarChar, 50, website);
               
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.Text, 1000, notes);
                this.Factory.AddParameter(cmd, "@companyTypeId", SqlDbType.Int, 10, companyTypeId);
                this.Factory.AddParameter(cmd, "@clientTypeId", SqlDbType.Int, 100, clientTypeId);
                this.Factory.AddParameter(cmd, "@timeZone", SqlDbType.NVarChar, 10, timeZone);
                this.Factory.AddParameter(cmd, "@id", SqlDbType.NVarChar, 10, id);



                if ((relationStart == null) || (relationStart.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@relationStart", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@relationStart", SqlDbType.DateTime, 10, relationStart);

                if ((relationEnd == null) || (relationEnd.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@relationEnd", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@relationEnd", SqlDbType.DateTime, 10, relationEnd);

                this.Factory.ExecuteNonQuery(cmd);

             

            }
            nRtn = id;
            return nRtn; //retruning the data stored in the table
        }


        /// <summary>
        /// Get Company Typefrom ddlcontactTypes
        /// </summary>
        /// <returns></returns>
        public List<ContactTypeViewModel> GetCompanyType()
        {
            string sql = "SELECT id, description as companyType FROM ddlcontactTypes Order By companyType ";

            return this.Factory.ExecuteObjects<ContactTypeViewModel>(sql);

        }

/// <summary>
/// Get Client Type from ddlClient Type
/// 
/// </summary>
/// <returns></returns>
        public List<ClientTypeViewModel> GetClientType()
        {
            string sql = "SELECT id, clientTypeName as clientType FROM ddlClientType WHERE clientTypeName  like '%NONE%'UNION ALL SELECT id, clientTypeName as clientType  FROM ddlClientType WHERE clientTypeName <> 'None'Order By clientType";

            return this.Factory.ExecuteObjects<ClientTypeViewModel>(sql);

        }
   
            /// <summary>
            /// Get data to fill company grid
            /// </summary>
            /// <returns></returns>

        public List<CompanyGridViewModel> GetAll()
        {
            string sql = "select streetOne+' '+streetTwo as address, st.state as stateName, " +
                  " ct.description as companyType, dct.clientTypeCode as clientType, cp.* from ddlCompany cp " +
                  " INNER JOIN ddlStates st on st.id = cp.stateId" +
                  " LEFT JOIN ddlcontactTypes ct ON ct.id = cp.companyTypeId" +
                  " LEFT JOIN ddlClientType dct ON dct.id = cp.clientTypeId";

            return this.Factory.ExecuteObjects<CompanyGridViewModel>(sql);

        }

        /// <summary>
        /// Get company data by user
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public List<CompanyGridViewModel> GetCompanyByLoggedInUser(string username)
        {
            string sql = "select streetOne+' '+streetTwo as address, st.state as stateName, " +
                  " ct.description as companyType, dct.clientTypeCode as clientType, cp.* from ddlCompany cp " +
                  " INNER JOIN ddlStates st on st.id = cp.stateId" +
                  " LEFT JOIN ddlcontactTypes ct ON ct.id = cp.companyTypeId" +
                  " LEFT JOIN ddlClientType dct ON dct.id = cp.clientTypeId  WHERE lower(cp.CreatedUser) = lower('" + username + "')    and  cp.relationStart = convert(date, GETDATE())    order by   cp.relationStart desc ";

            return this.Factory.ExecuteObjects<CompanyGridViewModel>(sql);

        }

        /// <summary>
        /// Get Companies by Id
        /// </summary>
        /// <param name="CompanyId"></param>
        /// <returns></returns>

        public DataTable GetCompanyByID(int CompanyId)
        {
            string sql = "select * from ddlCompany WHERE Id = " + CompanyId;

            return this.Factory.ExecuteTable(sql);
        }
    }

}
